DBFtoSQL - 'Fixed up' by: Pete Ness scream@primenet.com CID: 102347,710 This program was not originally written by me, I got it's raw state from Glynn Owen, Compuserve ID# 71073,2552 off the Delphi Forum. It wasn't quite what I was looking for, but had some potential. I've re-done just about everything since, but the original author deserve credit for the original work - the BatchMove component started as his idea. The original author used this to import DBF's to Interbase SQL. There are really no utilities out there that I've found to do this well - thus I wrote my own. I use Microsoft SQL 6.0 and needed to import data from Foxpro files. This was originally written to go from DBF's to SQL only, but it should work to move data from any ODBC source to any other ODBC source (through the BDE). I'm still having some problems with SQL 6.0 blocking the inserts after the first 75 - 150 records on some tables - most tables seem to work fine, however. If anyone has this problem with this, let me know and we'll figure out how to fix it. I will also upload a fixed version when I find the problem. **************** Glynn's Original Text (CID#71073,2552) ********************* This utility uses the TBatchMove component to copy a dBase database to an Interbase table. This is the third version of the program that I have written so far. The first used a parameterized insert that had some problems with datatypes. The second handed off the processing to the background, and inserted records one at a time. This version did allow me to specify the correspondence between the datatypes of dBase and Interbase, but was very slow. This final version will copy 10,000 records in less than 2 minutes on my 60Mhz Pentium system. This utility has been really handy in helping me to learn something about how components work, as well as being a very useful tool for setting up IB tables that have some familiar content. *************** End of Original Author's Documentation ********************** This program uses BDE alias to copy data from one database to another. Delphi's TBatchMove component does all the conversion of data types. Compile the program using Delphi - I have Apollo and had some initial problems with this app when Apollo was installed, so if you have problems, link up a DCL without. Before you can do any converting, you need to run the BDE config program to set up aliases to the data you want to convert to and from. You CAN use existing aliases. Run the program. Select the source alias from the drop down box. If the source database is a NON-SQL type and you didn't specify a path in the Alias setup, you can specify a path now. Otherwise, the Alias path will be displayed but you won't be able to change it. Select a file to convert (pretty straight forward stuff so far). Do the same thing for the destination. Pick the alias, path and table name. Next, select what you are want to do with the table. I've used append the most - Append uses the existing structure of your destination file and converts the data to fit it. If you want to create a new table, pick Create. I have not tested the other 3 options, although these setting are directly tied to the setting in the Delphi TBatchMove Component - see your Delphi documention if you have any questions. OK, next select Convert. The program will pull up a field list of all the fields in your source file and create a text file with lines in the form of DESTINATION=SOURCE Initially, these will be the same, but you can specify the field name for the destination if it's different, or delete fields altogether. Be aware if you are creating a SQL table that many "Normal" dbf field names are now illegal - most notably for me, CURRENT can no longer be a field name. If you pick an invalid name, the program will tell you and you can change it to something else. Before you actually do the conversion, Save the field setup you've just edited so you can re-load it if something goes wrong. Now, hit convert and away it goes - if everything is right. If anything isn't quite right, you'll get an error and you can go fix it - all the setting should stay the same until you exit the program. The progress bar can slow you down quite a bit as every time it updates, the TBatchMove component must re-initialize. Right now, it converts in 2% chunks - I figure that's a good tradeoff to see that it's working. If you think it slow you down too much, change the program - there's documentation at that part to show you how. If you have any problems, feedback, or cool changes you find/need/want/do, drop me a note. I think it would be fantastic to have a good data conversion tool for Delphi and I think this is a step in the right direction. Pete Ness scream@primenet.com CID: 102347,710 History..................................................................... First Version Uploaded to Compuserve on 9/15/95 12:05 am. 9/15/95 6:00pm Found a couple of problems (I didn't do the final testing with SQL server and I introduced a couple of problems) Added a parameter edit memo field so the password could be set up once and not be re-entered at every access. After specifying the Alias you want to use, hit the Property speed button (right beside the alias field) to pull up a list of parameters from the BDE. It now changes directory before getting the file list or opening either the source or destination TTables so that ODBC drivers set up to pull files from the current directory would work better. Set the path to empty if there is none, instead of garbage characters. Added hints to the main form. 9/15/95 7:30 pm Upload fixed version to Compuserve.